﻿using System;
using System.Data.SqlClient;

class BrowseUpdateNW
{
    static void Main()
    {
        SqlConnection con = new SqlConnection();
        con.ConnectionString = "Server=.\\SQLEXPRESS; " +
        "Database=Northwind; Integrated Security=true";
        con.Open();
        using (con)
        {
            //InsertIntoProducts(con);
            //PrintCategoriesAndProductName(con);                
        }
    }

    private static void InsertIntoProducts(SqlConnection con)
    {
        string input = "s";
        while (input != "")
        {
            SqlCommand insertProductName = new SqlCommand();
            insertProductName.Connection = con;
            insertProductName.CommandText = "insert into Products(ProductName,Discontinued) " +
                                            "values (@Name,0)";
            Console.Write("Value to insert?");
            input = Console.ReadLine();
            insertProductName.Parameters.Add(new SqlParameter("@Name", input));
            insertProductName.ExecuteNonQuery();
        }
    }

    private static void PrintCategoriesAndProductName(SqlConnection con)
    {
        SqlCommand command = new SqlCommand();
        command.Connection = con;
        command.CommandText = "select CategoryName,ProductName " +
                              "from Categories c inner join Products p " +
                              "on c.CategoryID=p.CategoryID " +
                              "order by CategoryName";
        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
        {
            string categoryName = reader["CategoryName"] as string;
            string productName = reader["ProductName"] as string;
            Console.WriteLine("{0} {1}", categoryName, productName);
        }
    }
}